-- Script n°13: adds the location tables (country, department, city) to replace the related enumeration.
-- @author Clément HELIOU (clement.heliou@gmail.com)
-- @since 1.0

BEGIN;

-- Drop objects if any.
DROP TABLE IF EXISTS city;
DROP TABLE IF EXISTS department;
DROP TABLE IF EXISTS country;

DROP SEQUENCE IF EXISTS seq_city;
DROP SEQUENCE IF EXISTS seq_country;
DROP SEQUENCE IF EXISTS seq_department;

-- Create the sequences.
CREATE SEQUENCE seq_city
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

ALTER TABLE seq_city OWNER TO chelioudbdev;
GRANT ALL ON TABLE seq_city TO chelioudbdev;
GRANT SELECT, UPDATE ON TABLE seq_city TO cheliouwebdev;

CREATE SEQUENCE seq_country
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

ALTER TABLE seq_country OWNER TO chelioudbdev;
GRANT ALL ON TABLE seq_country TO chelioudbdev;
GRANT SELECT, UPDATE ON TABLE seq_country TO cheliouwebdev;

CREATE SEQUENCE seq_department
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

ALTER TABLE seq_department OWNER TO chelioudbdev;
GRANT ALL ON TABLE seq_department TO chelioudbdev;
GRANT SELECT, UPDATE ON TABLE seq_department TO cheliouwebdev;

-- Create the tables
CREATE TABLE country (
   id		NUMERIC 		NOT NULL DEFAULT nextval('seq_country'::regclass),
   labelkey	VARCHAR(255)	NOT NULL,
   CONSTRAINT pk_country PRIMARY KEY (id)
)WITH (
  OIDS=FALSE
);

ALTER TABLE country OWNER TO chelioudbdev;
GRANT ALL ON TABLE country TO chelioudbdev;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE country TO cheliouwebdev;

CREATE TABLE department (
   id				NUMERIC			NOT NULL DEFAULT nextval('seq_department'::regclass),
   name				VARCHAR(100)	NOT NULL,
   zipcodeprefix	VARCHAR(3)		NOT NULL,
   countryid        NUMERIC			NOT NULL,
   CONSTRAINT pk_department PRIMARY KEY (id)
)WITH (
  OIDS=FALSE
);

ALTER TABLE department
   ADD CONSTRAINT fk_department_country FOREIGN KEY (countryid)
      REFERENCES country (id)
      ON DELETE RESTRICT ON UPDATE RESTRICT;
      
ALTER TABLE department OWNER TO chelioudbdev;
GRANT ALL ON TABLE department TO chelioudbdev;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE department TO cheliouwebdev;

CREATE TABLE city (
   id				NUMERIC			NOT NULL DEFAULT nextval('seq_city'::regclass),
   name				VARCHAR(100)	NOT NULL,
   zipcode			VARCHAR(10)		NOT NULL,
   countryid        NUMERIC			NOT NULL,
   departmentid		NUMERIC,
   CONSTRAINT pk_city PRIMARY KEY (id)
)WITH (
  OIDS=FALSE
);

ALTER TABLE city
   ADD CONSTRAINT fk_city_country FOREIGN KEY (countryid)
      REFERENCES country (id)
      ON DELETE RESTRICT ON UPDATE RESTRICT;
      
ALTER TABLE city
   ADD CONSTRAINT fk_city_department FOREIGN KEY (departmentid)
      REFERENCES department (id)
      ON DELETE RESTRICT ON UPDATE RESTRICT;
      
ALTER TABLE city OWNER TO chelioudbdev;
GRANT ALL ON TABLE city TO chelioudbdev;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE city TO cheliouwebdev;

-- Add the column for city into the photo table.
-- The constraints (unicity and foreign key) will be added in the following script.
ALTER TABLE photo ADD COLUMN cityid NUMERIC;

END;